package com.weiweiqin.utils;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sun.xml.internal.messaging.saaj.packaging.mime.internet.MimeUtility;
import com.weiweiqin.vo.common.CartVO;
import com.weiweiqin.vo.common.OrderTabVO;

public class ExcelUtil {
	
	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
	
	private static  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
	private static String[] titles = new String[]{"下单时间","商品信息","价格","收件人","手机号码","附言","地址","备忘"};
	
	private static String[] sfTitles = new String[]{"订单号","运单号","商品名称","商品价格(元)","运费(元)","物流公司编号","买家姓名","所在省份","所在市/县","所在区/县","买家详细地址","邮编","固定电话","手机号码"};
	
	
	
	public static void exportByList(List<OrderTabVO> orderList,HttpServletRequest request,HttpServletResponse response){
		logger.info("Enter method exportByList....");
		WritableWorkbook workbook = null;
		Date date = new Date();
		String format = sdf.format(date);
		String filename = "订单("+format+").xls";//设置下载时客户端Excel的名称     
        filename = encodeFilename(filename, request);//处理中文文件名  
        response.setContentType("application/vnd.ms-excel");     
        response.setHeader("Content-disposition", "attachment;filename=" + filename);     
		try {
			OutputStream os = response.getOutputStream(); // 页面输出流，jsp/servlet都可以   
			//String fileName = "D:"+File.separator+"testexcel"+File.separator+"output.xls";
			workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet("订单 Sheet", 0);
			sheet.setColumnView(0, 20);
			sheet.setColumnView(1, 135);
			sheet.setColumnView(2, 10);
			sheet.setColumnView(4, 20);
			sheet.setColumnView(7, 10);
			sheet.setColumnView(8, 66);
			for(int i = 0 ;i<titles.length;i++){
				Label label = new Label(i, 0, titles[i]);
				sheet.addCell(label);
			}
			int j =1;
			Label label  = null;
			if(!orderList.isEmpty()){
				for (OrderTabVO order : orderList) {
					if(null != order){
						for(int n = 0;n<titles.length;n++){
							//private static String[] titles = new String[]{"下单时间","商品标题","收件人","手机号码","尺码","颜色","附言","地址","备忘"};
							switch (n) {
							case 0:
								label = new Label(n, j,order.getUpdateDate());
								sheet.addCell(label);
								break;
							case 1:
								List<CartVO> goodsList = order.getGoodsList();
								if(null != goodsList){
									String info = "";
									for (CartVO cartVO : goodsList) {
										if(null != cartVO.getGoods() && null != cartVO.getGoods().getTitle()){
											String item = cartVO.getGoods().getTitle();
											info = info +item+"×"+cartVO.getNum()+"，"+"颜色："+cartVO.getColor()+"，尺码："+cartVO.getSize()+"；";
										}
									}
									label = new Label(n, j,info);
									sheet.addCell(label);
								}
								
								break;
							case 2:
								if(null != order.getOrder().getTotalPrice()){
									label = new Label(n, j,String.valueOf(order.getOrder().getTotalPrice()));
									sheet.addCell(label);
								}
								break;
							case 3:
								label = new Label(n, j,order.getOrder().getName());
								sheet.addCell(label);
								break;
							case 4:
								label = new Label(n, j,order.getOrder().getMobile());
								sheet.addCell(label);
								break;
							case 5:
								label = new Label(n, j,order.getOrder().getRemark());
								sheet.addCell(label);
								break;
							case 6:
								label = new Label(n, j,order.getOrder().getAddr());
								sheet.addCell(label);
								break;
							case 7:
								label = new Label(n, j,order.getOrder().getMemo());
								sheet.addCell(label);
								break;
							default:
								break;
							}
						}
						j++;
					}
				}
			}
	        workbook.write();     
	       workbook.close(); 
		} catch (Exception e) {
			logger.error("导出数据失败...",e);
		}
		
	}
	
	/**
	 * 导出顺丰模板格式
	 * @param orderList
	 * @param request
	 * @param response
	 */
	public static void exportSF(List<OrderTabVO> orderList,HttpServletRequest request,HttpServletResponse response){
		logger.info("Enter method exportSF....");
		WritableWorkbook workbook = null;
		Date date = new Date();
		String format = sdf.format(date);
		String filename = "顺丰("+format+").xls";//设置下载时客户端Excel的名称     
        filename = encodeFilename(filename, request);//处理中文文件名  
        response.setContentType("application/vnd.ms-excel");     
        response.setHeader("Content-disposition", "attachment;filename=" + filename);     
		try {
			OutputStream os = response.getOutputStream(); // 页面输出流，jsp/servlet都可以   
			//String fileName = "D:"+File.separator+"testexcel"+File.separator+"output.xls";
			workbook = Workbook.createWorkbook(os);
			WritableSheet sheet = workbook.createSheet("订单 Sheet", 0);
			sheet.setColumnView(0, 20);
			sheet.setColumnView(1, 20);
			sheet.setColumnView(2, 20);
			sheet.setColumnView(10, 60);
			 
			Label label1 = new Label(0, 0, "总笔数");
			Label label2 = new Label(1, 0, "商品总金额(元)");
			Label label3 = new Label(2, 0, "运费总金额(元)");
			sheet.addCell(label1);
			sheet.addCell(label2);
			sheet.addCell(label3);
			
			for(int i = 0 ;i<sfTitles.length;i++){
				Label label = new Label(i, 2, sfTitles[i]);
				sheet.addCell(label);
			}
		 
			int j =3;
			Label label  = null;
			jxl.write.Number labelNF = null;
			double totalPrice = 0;
			if(!orderList.isEmpty()){
				for (OrderTabVO order : orderList) {
					totalPrice = totalPrice+order.getOrder().getTotalPrice().doubleValue();
					if(null != order){
						for(int n = 0;n<sfTitles.length;n++){
							//private static String[] titles = new String[]{"下单时间","商品标题","收件人","手机号码","尺码","颜色","附言","地址","备忘"};
							switch (n) {
							case 0:
								break;
							case 1:
								break;
							case 2:
								label = new Label(n, j,"礼品");
								sheet.addCell(label);
								break;
							case 3:
								labelNF = new jxl.write.Number(n, j, Double.valueOf(order.getOrder().getTotalPrice()+"")); //格式化数值
								sheet.addCell(labelNF);
								break;
							case 4:
								labelNF = new jxl.write.Number(n, j, 0); //格式化数值
								sheet.addCell(labelNF);
								break;
							case 5:
								label = new Label(n, j,"SF");
								sheet.addCell(label);
								break;
							case 6:
								label = new Label(n, j,order.getOrder().getName());
								sheet.addCell(label);
								break;
							case 7:
								label = new Label(n, j,order.getOrder().getProvince());
								sheet.addCell(label);
								break;
							case 8:
								label = new Label(n, j,order.getOrder().getCity());
								sheet.addCell(label);
								break;
								
							case 9:
								label = new Label(n, j,order.getOrder().getArea());
								sheet.addCell(label);
								break;
								
								
							case 10:
								label = new Label(n, j,order.getOrder().getAddr());
								sheet.addCell(label);
								break;
								
							case 11:
								label = new Label(n, j,"100000");
								sheet.addCell(label);
								break;
								
							case 12:
								break;
								
							case 13:
								label = new Label(n, j,order.getOrder().getMobile());
								sheet.addCell(label);
								break;
							default:
								break;
							}
						}
						j++;
					}
				}
				jxl.write.Number labelNF1 = new jxl.write.Number(0, 1, orderList.size());
				jxl.write.Number labelNF2 = new jxl.write.Number(1, 1, totalPrice);
				jxl.write.Number labelNF3 = new jxl.write.Number(2, 1, 0);
				sheet.addCell(labelNF1);
				sheet.addCell(labelNF2);
				sheet.addCell(labelNF3);
			}
	        workbook.write();     
	       workbook.close(); 
		} catch (Exception e) {
			logger.error("导出数据失败...",e);
		}
		
	}
	
	/**  
     * 设置下载文件中文件的名称  
     *   
     * @param filename  
     * @param request  
     * @return  
     */    
    public static String encodeFilename(String filename, HttpServletRequest request) {    
      /**  
       * 获取客户端浏览器和操作系统信息  
       * 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)  
       * 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.7.10) Gecko/20050717 Firefox/1.0.6  
       */    
      String agent = request.getHeader("USER-AGENT");    
      try {    
        if ((agent != null) && (-1 != agent.indexOf("MSIE"))) {    
          String newFileName = URLEncoder.encode(filename, "UTF-8");    
          newFileName = StringUtils.replace(newFileName, "+", "%20");    
          if (newFileName.length() > 150) {    
            newFileName = new String(filename.getBytes("GB2312"), "ISO8859-1");    
            newFileName = StringUtils.replace(newFileName, " ", "%20");    
          }    
          return newFileName;    
        }    
        if ((agent != null) && (-1 != agent.indexOf("Mozilla")))    
          return MimeUtility.encodeText(filename, "UTF-8", "B");    
      
        return filename;    
      } catch (Exception ex) {    
        return filename;    
      }    
    }   
	
}
